﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using bdxyit.com.RecruitmentAppSys.Models;
using System.Data.SqlClient;
using System.Data;
using bdxyit.com.RecruitmentAppSys.DBUtility;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：ProfessionalList
*系统时间：3/25/2012 3:36:36 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:36:36 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class ProfessionalListDAL : IProfessionalListDAL
    {
        private const string SQL_SELECT_PROFESSIONALLIST = " select  top 1 ProID,ProName,ProTypeID from ProfessionalLists ";
        private const string SQL_SELECT_PROFESSIONALLIST_BY_PROID = " select  top 1 ProID,ProName,ProTypeID from ProfessionalLists "
                                                                   + "  where ProID=@ProID";
        private const string SQL_DELETE_PROFESSIONALLIST_BY_PROID = " delete from ProfessionalLists "
                                                                  + "  where ProID=@ProID";
        private const string SQL_UPDATE_PROFESSIONALLIST_BY_PROID = " update ProfessionalLists set "
                                                                    + " ProID=@ProID,"
                                                                    + " ProName=@ProName,"
                                                                    + " ProTypeID=@ProTypeID"
                                                                    + "  where ProID=@ProID";
        private const string SQL_INSERT_PROFESSIONALLIST = " insert into ProfessionalLists("
                                                        + " ProID,ProName,ProTypeID)"
                                                        + "  values ("
                                                        + " @ProID,@ProName,@ProTypeID)";
        private const string FIELD_ProID = "@ProID";
        private const string FIELD_ProName = "@ProName";
        private const string FIELD_ProTypeID = "@ProTypeID";
        public bool InsertProfessionalList(ProfessionalList cmodel)
        {
            bool flag = false;
            SqlParameter[] ProfessionalListParms = GetProfessionalListParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            ProfessionalListParms[0].Value = cmodel.ProID;
            ProfessionalListParms[1].Value = cmodel.ProName;
            ProfessionalListParms[2].Value = cmodel.ProTypeID;

            foreach (SqlParameter parm in ProfessionalListParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_PROFESSIONALLIST;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool DeleteProfessionalList(string ProID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_ProID, SqlDbType.VarChar, 10);
            parm.Value = ProID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_PROFESSIONALLIST_BY_PROID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public bool UpdateProfessionalList(ProfessionalList cmodel)
        {
            bool flag = false;
            SqlParameter[] ProfessionalListParms = GetProfessionalListParameters();
            SqlCommand cmd = new SqlCommand();
            //写添赋值语句
            ProfessionalListParms[0].Value = cmodel.ProID;
            ProfessionalListParms[1].Value = cmodel.ProName;
            ProfessionalListParms[2].Value = cmodel.ProTypeID;

            foreach (SqlParameter parm in ProfessionalListParms)
                cmd.Parameters.Add(parm);
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_PROFESSIONALLIST_BY_PROID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }
        public ProfessionalList SelectProfessionalListByProID(string ProID)
        {
            ProfessionalList cmodel = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_ProID, SqlDbType.VarChar, 10);
            parm.Value = ProID;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_PROFESSIONALLIST_BY_PROID, parm))
            {
                if (dr.Read())
                {
                    cmodel = new ProfessionalList();
                    if (dr.HasRows == true)
                    {
                        if (dr["ProID"] != null && dr["ProID"].ToString() != "")
                        {
                            cmodel.ProID = dr["ProID"].ToString();
                        }
                        if (dr["ProName"] != null && dr["ProName"].ToString() != "")
                        {
                            cmodel.ProName = dr["ProName"].ToString();
                        }
                        if (dr["ProTypeID"] != null && dr["ProTypeID"].ToString() != "")
                        {
                            cmodel.ProTypeID = dr["ProTypeID"].ToString();
                        }


                    }
                }
            }
            return cmodel;
        }
        public List<ProfessionalList> SelectProfessionalList()
        {
            List<ProfessionalList> selectlist = new List<ProfessionalList>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_PROFESSIONALLIST))
            {
                while (dr.Read())
                {
                    ProfessionalList cmodel = new ProfessionalList();
                    //写添赋值语句
                    if (dr["ProID"] != null && dr["ProID"].ToString() != "")
                    {
                        cmodel.ProID = dr["ProID"].ToString();
                    }
                    if (dr["ProName"] != null && dr["ProName"].ToString() != "")
                    {
                        cmodel.ProName = dr["ProName"].ToString();
                    }
                    if (dr["ProTypeID"] != null && dr["ProTypeID"].ToString() != "")
                    {
                        cmodel.ProTypeID = dr["ProTypeID"].ToString();
                    }

                    selectlist.Add(cmodel);
                }
            }
            return selectlist;
        }
        private SqlParameter[] GetProfessionalListParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter(FIELD_ProID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_ProName, SqlDbType.VarChar,50),
					new SqlParameter(FIELD_ProTypeID, SqlDbType.VarChar,10)};
            return parms;
        }
    
    }
}